﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for TuVanDAL
/// </summary>
public class TuVanDAL
{
	public TuVanDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public DataTable fnG_GetAllDataTuVan()
    {
        string strSQL = "";
        strSQL = "select *,Substring(gioithieu,0,80+CHARINDEX(' ',Substring(gioithieu,80,DATALENGTH(gioithieu)))) as [GioiThieu] from TuVan order by idtv DESC";
        return DataProvider.GetData(strSQL);
    }

    public TuVanEntity fnG_GetTuVan(string MaTinTuc)
    {
        string strSQL = "SELECT  * FROM TuVan WHERE idtv=@Matin";
        DataTable table = DataProvider.GetData(strSQL, "@Matin", MaTinTuc);
        TuVanEntity tuvan = new TuVanEntity();
        tuvan.HinhDaiDien = table.Rows[0]["HinhDaiDien"].ToString();
        tuvan.Idtv = int.Parse(table.Rows[0]["Idtv"].ToString());
        tuvan.NgayNhap = DateTime.Parse(table.Rows[0]["NgayNhap"].ToString());
        tuvan.NoiDung = table.Rows[0]["NoiDung"].ToString();
        tuvan.TieuDe = table.Rows[0]["TieuDe"].ToString();
        tuvan.GioiThieu = table.Rows[0]["GioiThieu"].ToString();
        tuvan.Nguon = table.Rows[0]["Nguon"].ToString();
        return tuvan;
    }

    internal DataTable fnG_Get4TuVan()
    {
        string strSQL = "";
        strSQL = "select top 4 *,Substring(GioiThieu,0,80+CHARINDEX(' ',Substring(GioiThieu,80,DATALENGTH(GioiThieu)))) as [GioiThieu1] from TuVan order by NgayNhap DESC";
        return DataProvider.GetData(strSQL);
    }
    // cac tin tu van lam dep khac
    public DataTable fnG_GetData10TuVanDiff(TuVanEntity TuVan)
    {
        string strSQL = "";
        strSQL = "select top 10 *,'TuVan' as TinTuc from TUVAN WHERE idtv NOT IN (select idtv from TUVAN where idtv=@idtv)";
        return DataProvider.GetData(strSQL,"@idtv",TuVan.Idtv);
    }
    // insert tu van
    public int fnG_InsertTuVan(TuVanEntity TuVan)
    {
        string sql = "INSERT INTO TuVan VALUES(@TieuDe,@GioiThieu ,@NgayNhap,@NoiDung,@HinhDaiDien,@Nguon)";
        return DataProvider.ExecuteNonQuery(sql, "@TieuDe", TuVan.TieuDe, "@GioiThieu", TuVan.GioiThieu, "@NgayNhap", TuVan.NgayNhap, "@NoiDung", TuVan.NoiDung, "@HinhDaiDien", TuVan.HinhDaiDien, "@Nguon", TuVan.Nguon);
    }
    //update
    public int fnG_UpdateTuVan(TuVanEntity TuVan)
    {
        string sql = "Update TuVan Set TieuDe=@TieuDe,GioiThieu=@GioiThieu ,NgayNhap=@NgayNhap,NoiDung=@NoiDung,HinhDaiDien=@HinhDaiDien,Nguon=@Nguon where idtv=@idtv";
        return DataProvider.ExecuteNonQuery(sql, "@TieuDe", TuVan.TieuDe, "@GioiThieu", TuVan.GioiThieu, "@NgayNhap", TuVan.NgayNhap, "@NoiDung", TuVan.NoiDung, "@HinhDaiDien", TuVan.HinhDaiDien, "@Nguon", TuVan.Nguon, "@idtv", TuVan.Idtv);
    }
    //delete
    public int fnG_DeleteTuVan(TuVanEntity TuVan)
    {
        string sql = "delete from TuVan where idtv=@idtv";
        return DataProvider.ExecuteNonQuery(sql, "@idtv", TuVan.Idtv);

    }
    //active
    public int fnG_ActiveTuVan(TuVanEntity TuVan)
    {
        string sql = "Update TuVan Set NgayNhap=@NgayNhap where idtv=@idtv";
        return DataProvider.ExecuteNonQuery(sql, "@NgayNhap",DateTime.Now,"@idtv",TuVan.Idtv);
    }
    public DataTable fnG_GetDataForDate(DateTime tungay, DateTime denngay)
    {
        string sql = "select * from TuVan where datediff(day,@tungay,NgayNhap)>=0 and datediff(day,NgayNhap,@denngay)>=0";
        return DataProvider.GetData(sql, "@tungay", tungay, "@denngay", denngay);
    }
    /// <summary>
    /// Tìm Kiếm Tư Vấn
    /// </summary>
    /// <returns></returns>
    public DataTable fnG_TimKiemTuVan(string timkiem)
    {
        string sql = "select * from TuVan where (select dbo.funcNVarchar(TieuDe)) like (select dbo.funcNVarchar(@timkiem));";
        return DataProvider.GetData(sql, "@timkiem", timkiem);
    }
}